package com.sample.javabean;

import java.sql.*;

import com.sample.action.form.FamilyForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 定义模型，封装属性及方法
 * @author 演示
 *
 */
public class Family {

	private Integer id;
	private String cityno;
	private String zhenno;
	private String zuno;
	private String jiano;
	private String name;
	private String idcard;
	private String cunno;
	private String time;
	// 实例化JdbcUtils组件，完成加载驱动以及连接数据库操作，获得连接实例
	private JdbcUtils jdbcUtils = new JdbcUtils();
	
	
	
	public Family(Integer id){
		this.id =id;
	}
	/**
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_family where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.id =rs.getInt("id");
				this.cityno =rs.getString("cityno");
				this.zhenno =rs.getString("zhenno");
				this.zuno =rs.getString("zuno");
				this.jiano =rs.getString("jiano");
				this.name =rs.getString("name");
				this.idcard =rs.getString("idcard");
				this.cunno =rs.getString("cunno");
				this.time =rs.getString("time");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加
	 */
	public void addFamily() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_family(cityno,zhenno,zuno,jiano,name,idcard,cunno,time) values('"
					+this.cityno+"','" + this.zhenno + "','" + this.zuno + "','"+this.jiano+"','"+this.name+"','"+this.idcard+"','"+this.cunno+"','"+this.time+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void editFamily() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_family set cityno='"+this.cityno+"',zhenno='"+this.zhenno+"',zuno='"+this.zuno+"',jiano='"+this.jiano+"',name='"+this.name+"',idcard='"+this.idcard+"',cunno='"+this.cunno+"',time='"+this.time+"' where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void delFamily() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_family  where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示信息
	 * @return
	 */
	public Pager<Family> list(int page,int size,FamilyForm familyForm,String keywords) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Family> familys=new Pager<Family>();
		familys.setPage(page);
		familys.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_family where 1=1";
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//模糊查询
			if(null != keywords && "" != keywords && !"".equals(keywords)){
				selectSql = selectSql+"   and (name like '%"+keywords+"%' or idcard like '%"+keywords+"%' or jiano like '%"+keywords+"%')";
			}
			
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 familys.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
				  int id=rs.getInt("id");
				  String cityno=rs.getString("cityno");
				  String zhenno=rs.getString("zhenno");
				  String zuno=rs.getString("zuno");
				  String jiano=rs.getString("jiano");
				  String name=rs.getString("name");
				  String idcard=rs.getString("idcard");
				  String cunno=rs.getString("cunno");
				  String time=rs.getString("time");
				  Family family=new Family(id,cityno,zhenno,zuno,jiano,name,idcard,cunno,time);
				  familys.getDataSets().add(family);
			 }
			 familys.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return familys;

	}

	//定义无参构造函数
	public Family(){
		
	}
	public Family(Integer id, String cityno, String zhenno, String zuno,
			String jiano, String name, String idcard, String cunno,
			String time) {
		super();
		this.id = id;
		this.cityno = cityno;
		this.zhenno = zhenno;
		this.zuno = zuno;
		this.jiano = jiano;
		this.name = name;
		this.idcard = idcard;
		this.cunno = cunno;
		this.time = time;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getCityno() {
		return cityno;
	}
	public void setCityno(String cityno) {
		this.cityno = cityno;
	}
	public String getZhenno() {
		return zhenno;
	}
	public void setZhenno(String zhenno) {
		this.zhenno = zhenno;
	}
	public String getZuno() {
		return zuno;
	}
	public void setZuno(String zuno) {
		this.zuno = zuno;
	}
	public String getJiano() {
		return jiano;
	}
	public void setJiano(String jiano) {
		this.jiano = jiano;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getIdcard() {
		return idcard;
	}
	public void setIdcard(String idcard) {
		this.idcard = idcard;
	}
	public String getCunno() {
		return cunno;
	}
	public void setCunno(String cunno) {
		this.cunno = cunno;
	}
	public String getTime() {
		return time;
	}
	public void setTime(String time) {
		this.time = time;
	}
	
}
